
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/14/2012 14:21:29
-- Generated from EDMX file: C:\Users\Administrator\Documents\Visual Studio 2010\Projects\AssessmentTool\DataLayer\Model1.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [assesstool];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CompetenzeCompetenze]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Competenze] DROP CONSTRAINT [FK_CompetenzeCompetenze];
GO
IF OBJECT_ID(N'[dbo].[FK_QuestionariCompetenze]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Competenze] DROP CONSTRAINT [FK_QuestionariCompetenze];
GO
IF OBJECT_ID(N'[dbo].[FK_UtenteRisposte]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Risposte] DROP CONSTRAINT [FK_UtenteRisposte];
GO
IF OBJECT_ID(N'[dbo].[FK_ProfiloCompetenza_Profilo]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProfiloCompetenza] DROP CONSTRAINT [FK_ProfiloCompetenza_Profilo];
GO
IF OBJECT_ID(N'[dbo].[FK_ProfiloCompetenza_Competenza]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ProfiloCompetenza] DROP CONSTRAINT [FK_ProfiloCompetenza_Competenza];
GO
IF OBJECT_ID(N'[dbo].[FK_AnagraficaRispostaAnagraficaPesi]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AnagraficaPesiSet] DROP CONSTRAINT [FK_AnagraficaRispostaAnagraficaPesi];
GO
IF OBJECT_ID(N'[dbo].[FK_AnagraficaPesiCompetenza]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AnagraficaPesiSet] DROP CONSTRAINT [FK_AnagraficaPesiCompetenza];
GO
IF OBJECT_ID(N'[dbo].[FK_AnagraficaPesiProfilo_AnagraficaPesi]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AnagraficaPesiProfilo] DROP CONSTRAINT [FK_AnagraficaPesiProfilo_AnagraficaPesi];
GO
IF OBJECT_ID(N'[dbo].[FK_AnagraficaPesiProfilo_Profilo]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[AnagraficaPesiProfilo] DROP CONSTRAINT [FK_AnagraficaPesiProfilo_Profilo];
GO
IF OBJECT_ID(N'[dbo].[FK_AnagraficaPesiRisposta]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Risposte] DROP CONSTRAINT [FK_AnagraficaPesiRisposta];
GO
IF OBJECT_ID(N'[dbo].[FK_GruppoUtente]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Utenti] DROP CONSTRAINT [FK_GruppoUtente];
GO
IF OBJECT_ID(N'[dbo].[FK_CompagniaUtente]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Utenti] DROP CONSTRAINT [FK_CompagniaUtente];
GO
IF OBJECT_ID(N'[dbo].[FK_ModelloCompagnia]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Compagnie] DROP CONSTRAINT [FK_ModelloCompagnia];
GO
IF OBJECT_ID(N'[dbo].[FK_UtenteSessione]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Sessioni] DROP CONSTRAINT [FK_UtenteSessione];
GO
IF OBJECT_ID(N'[dbo].[FK_SessioneRisposta]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Risposte] DROP CONSTRAINT [FK_SessioneRisposta];
GO
IF OBJECT_ID(N'[dbo].[FK_StatoSessioneSessione]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Sessioni] DROP CONSTRAINT [FK_StatoSessioneSessione];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Utenti]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Utenti];
GO
IF OBJECT_ID(N'[dbo].[Modelli]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Modelli];
GO
IF OBJECT_ID(N'[dbo].[Competenze]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Competenze];
GO
IF OBJECT_ID(N'[dbo].[AnagRisposte]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AnagRisposte];
GO
IF OBJECT_ID(N'[dbo].[Risposte]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Risposte];
GO
IF OBJECT_ID(N'[dbo].[ProfiloSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProfiloSet];
GO
IF OBJECT_ID(N'[dbo].[AnagraficaPesiSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AnagraficaPesiSet];
GO
IF OBJECT_ID(N'[dbo].[Gruppi]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Gruppi];
GO
IF OBJECT_ID(N'[dbo].[Compagnie]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Compagnie];
GO
IF OBJECT_ID(N'[dbo].[Sessioni]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Sessioni];
GO
IF OBJECT_ID(N'[dbo].[StatiSessione]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StatiSessione];
GO
IF OBJECT_ID(N'[dbo].[ProfiloCompetenza]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ProfiloCompetenza];
GO
IF OBJECT_ID(N'[dbo].[AnagraficaPesiProfilo]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AnagraficaPesiProfilo];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Utenti'
CREATE TABLE [dbo].[Utenti] (
    [Username] nvarchar(50)  NOT NULL,
    [Password] nvarchar(250)  NOT NULL,
    [Nome] nvarchar(250)  NULL,
    [Cognome] nvarchar(250)  NULL,
    [Email] nvarchar(250)  NULL,
    [Gruppo_Id] nvarchar(50)  NOT NULL,
    [Compagnia_Id] int  NOT NULL
);
GO

-- Creating table 'Modelli'
CREATE TABLE [dbo].[Modelli] (
    [Id] int  NOT NULL,
    [Nome] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Competenze'
CREATE TABLE [dbo].[Competenze] (
    [Id] int  NOT NULL,
    [Livello] int  NOT NULL,
    [Codice] nvarchar(max)  NOT NULL,
    [Padre_Id] int  NULL,
    [Modello_Id] int  NOT NULL
);
GO

-- Creating table 'AnagRisposte'
CREATE TABLE [dbo].[AnagRisposte] (
    [Id] int  NOT NULL,
    [Nome] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Risposte'
CREATE TABLE [dbo].[Risposte] (
    [Id] nvarchar(100)  NOT NULL,
    [Utente_Username] nvarchar(50)  NOT NULL,
    [AnagraficaPesi_Id] int  NOT NULL,
    [Sessione_Id] int  NOT NULL
);
GO

-- Creating table 'ProfiloSet'
CREATE TABLE [dbo].[ProfiloSet] (
    [Id] int  NOT NULL,
    [Nome] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'AnagraficaPesiSet'
CREATE TABLE [dbo].[AnagraficaPesiSet] (
    [Id] int  NOT NULL,
    [Peso] decimal(18,0)  NOT NULL,
    [AnagraficaRisposta_Id] int  NOT NULL,
    [Competenza_Id] int  NOT NULL
);
GO

-- Creating table 'Gruppi'
CREATE TABLE [dbo].[Gruppi] (
    [Id] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'Compagnie'
CREATE TABLE [dbo].[Compagnie] (
    [Id] int  NOT NULL,
    [Descrizione] nvarchar(max)  NOT NULL,
    [Lingua] nvarchar(max)  NOT NULL,
    [Modello_Id] int  NOT NULL
);
GO

-- Creating table 'Sessioni'
CREATE TABLE [dbo].[Sessioni] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [DataCreazione] datetime  NOT NULL,
    [Utente_Username] nvarchar(50)  NOT NULL,
    [StatoSessione_Id] nvarchar(50)  NOT NULL
);
GO

-- Creating table 'StatiSessione'
CREATE TABLE [dbo].[StatiSessione] (
    [Id] nvarchar(50)  NOT NULL,
    [Nome] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'ProfiloCompetenza'
CREATE TABLE [dbo].[ProfiloCompetenza] (
    [Profili_Id] int  NOT NULL,
    [Competenze_Id] int  NOT NULL
);
GO

-- Creating table 'AnagraficaPesiProfilo'
CREATE TABLE [dbo].[AnagraficaPesiProfilo] (
    [AnagraficaPesi_Id] int  NOT NULL,
    [Profilo_Id] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Username] in table 'Utenti'
ALTER TABLE [dbo].[Utenti]
ADD CONSTRAINT [PK_Utenti]
    PRIMARY KEY CLUSTERED ([Username] ASC);
GO

-- Creating primary key on [Id] in table 'Modelli'
ALTER TABLE [dbo].[Modelli]
ADD CONSTRAINT [PK_Modelli]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Competenze'
ALTER TABLE [dbo].[Competenze]
ADD CONSTRAINT [PK_Competenze]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AnagRisposte'
ALTER TABLE [dbo].[AnagRisposte]
ADD CONSTRAINT [PK_AnagRisposte]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Risposte'
ALTER TABLE [dbo].[Risposte]
ADD CONSTRAINT [PK_Risposte]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ProfiloSet'
ALTER TABLE [dbo].[ProfiloSet]
ADD CONSTRAINT [PK_ProfiloSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'AnagraficaPesiSet'
ALTER TABLE [dbo].[AnagraficaPesiSet]
ADD CONSTRAINT [PK_AnagraficaPesiSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Gruppi'
ALTER TABLE [dbo].[Gruppi]
ADD CONSTRAINT [PK_Gruppi]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Compagnie'
ALTER TABLE [dbo].[Compagnie]
ADD CONSTRAINT [PK_Compagnie]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Sessioni'
ALTER TABLE [dbo].[Sessioni]
ADD CONSTRAINT [PK_Sessioni]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StatiSessione'
ALTER TABLE [dbo].[StatiSessione]
ADD CONSTRAINT [PK_StatiSessione]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Profili_Id], [Competenze_Id] in table 'ProfiloCompetenza'
ALTER TABLE [dbo].[ProfiloCompetenza]
ADD CONSTRAINT [PK_ProfiloCompetenza]
    PRIMARY KEY NONCLUSTERED ([Profili_Id], [Competenze_Id] ASC);
GO

-- Creating primary key on [AnagraficaPesi_Id], [Profilo_Id] in table 'AnagraficaPesiProfilo'
ALTER TABLE [dbo].[AnagraficaPesiProfilo]
ADD CONSTRAINT [PK_AnagraficaPesiProfilo]
    PRIMARY KEY NONCLUSTERED ([AnagraficaPesi_Id], [Profilo_Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Padre_Id] in table 'Competenze'
ALTER TABLE [dbo].[Competenze]
ADD CONSTRAINT [FK_CompetenzeCompetenze]
    FOREIGN KEY ([Padre_Id])
    REFERENCES [dbo].[Competenze]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompetenzeCompetenze'
CREATE INDEX [IX_FK_CompetenzeCompetenze]
ON [dbo].[Competenze]
    ([Padre_Id]);
GO

-- Creating foreign key on [Modello_Id] in table 'Competenze'
ALTER TABLE [dbo].[Competenze]
ADD CONSTRAINT [FK_QuestionariCompetenze]
    FOREIGN KEY ([Modello_Id])
    REFERENCES [dbo].[Modelli]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionariCompetenze'
CREATE INDEX [IX_FK_QuestionariCompetenze]
ON [dbo].[Competenze]
    ([Modello_Id]);
GO

-- Creating foreign key on [Utente_Username] in table 'Risposte'
ALTER TABLE [dbo].[Risposte]
ADD CONSTRAINT [FK_UtenteRisposte]
    FOREIGN KEY ([Utente_Username])
    REFERENCES [dbo].[Utenti]
        ([Username])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UtenteRisposte'
CREATE INDEX [IX_FK_UtenteRisposte]
ON [dbo].[Risposte]
    ([Utente_Username]);
GO

-- Creating foreign key on [Profili_Id] in table 'ProfiloCompetenza'
ALTER TABLE [dbo].[ProfiloCompetenza]
ADD CONSTRAINT [FK_ProfiloCompetenza_Profilo]
    FOREIGN KEY ([Profili_Id])
    REFERENCES [dbo].[ProfiloSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Competenze_Id] in table 'ProfiloCompetenza'
ALTER TABLE [dbo].[ProfiloCompetenza]
ADD CONSTRAINT [FK_ProfiloCompetenza_Competenza]
    FOREIGN KEY ([Competenze_Id])
    REFERENCES [dbo].[Competenze]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ProfiloCompetenza_Competenza'
CREATE INDEX [IX_FK_ProfiloCompetenza_Competenza]
ON [dbo].[ProfiloCompetenza]
    ([Competenze_Id]);
GO

-- Creating foreign key on [AnagraficaRisposta_Id] in table 'AnagraficaPesiSet'
ALTER TABLE [dbo].[AnagraficaPesiSet]
ADD CONSTRAINT [FK_AnagraficaRispostaAnagraficaPesi]
    FOREIGN KEY ([AnagraficaRisposta_Id])
    REFERENCES [dbo].[AnagRisposte]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AnagraficaRispostaAnagraficaPesi'
CREATE INDEX [IX_FK_AnagraficaRispostaAnagraficaPesi]
ON [dbo].[AnagraficaPesiSet]
    ([AnagraficaRisposta_Id]);
GO

-- Creating foreign key on [Competenza_Id] in table 'AnagraficaPesiSet'
ALTER TABLE [dbo].[AnagraficaPesiSet]
ADD CONSTRAINT [FK_AnagraficaPesiCompetenza]
    FOREIGN KEY ([Competenza_Id])
    REFERENCES [dbo].[Competenze]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AnagraficaPesiCompetenza'
CREATE INDEX [IX_FK_AnagraficaPesiCompetenza]
ON [dbo].[AnagraficaPesiSet]
    ([Competenza_Id]);
GO

-- Creating foreign key on [AnagraficaPesi_Id] in table 'AnagraficaPesiProfilo'
ALTER TABLE [dbo].[AnagraficaPesiProfilo]
ADD CONSTRAINT [FK_AnagraficaPesiProfilo_AnagraficaPesi]
    FOREIGN KEY ([AnagraficaPesi_Id])
    REFERENCES [dbo].[AnagraficaPesiSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Profilo_Id] in table 'AnagraficaPesiProfilo'
ALTER TABLE [dbo].[AnagraficaPesiProfilo]
ADD CONSTRAINT [FK_AnagraficaPesiProfilo_Profilo]
    FOREIGN KEY ([Profilo_Id])
    REFERENCES [dbo].[ProfiloSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AnagraficaPesiProfilo_Profilo'
CREATE INDEX [IX_FK_AnagraficaPesiProfilo_Profilo]
ON [dbo].[AnagraficaPesiProfilo]
    ([Profilo_Id]);
GO

-- Creating foreign key on [AnagraficaPesi_Id] in table 'Risposte'
ALTER TABLE [dbo].[Risposte]
ADD CONSTRAINT [FK_AnagraficaPesiRisposta]
    FOREIGN KEY ([AnagraficaPesi_Id])
    REFERENCES [dbo].[AnagraficaPesiSet]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AnagraficaPesiRisposta'
CREATE INDEX [IX_FK_AnagraficaPesiRisposta]
ON [dbo].[Risposte]
    ([AnagraficaPesi_Id]);
GO

-- Creating foreign key on [Gruppo_Id] in table 'Utenti'
ALTER TABLE [dbo].[Utenti]
ADD CONSTRAINT [FK_GruppoUtente]
    FOREIGN KEY ([Gruppo_Id])
    REFERENCES [dbo].[Gruppi]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_GruppoUtente'
CREATE INDEX [IX_FK_GruppoUtente]
ON [dbo].[Utenti]
    ([Gruppo_Id]);
GO

-- Creating foreign key on [Compagnia_Id] in table 'Utenti'
ALTER TABLE [dbo].[Utenti]
ADD CONSTRAINT [FK_CompagniaUtente]
    FOREIGN KEY ([Compagnia_Id])
    REFERENCES [dbo].[Compagnie]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CompagniaUtente'
CREATE INDEX [IX_FK_CompagniaUtente]
ON [dbo].[Utenti]
    ([Compagnia_Id]);
GO

-- Creating foreign key on [Modello_Id] in table 'Compagnie'
ALTER TABLE [dbo].[Compagnie]
ADD CONSTRAINT [FK_ModelloCompagnia]
    FOREIGN KEY ([Modello_Id])
    REFERENCES [dbo].[Modelli]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModelloCompagnia'
CREATE INDEX [IX_FK_ModelloCompagnia]
ON [dbo].[Compagnie]
    ([Modello_Id]);
GO

-- Creating foreign key on [Utente_Username] in table 'Sessioni'
ALTER TABLE [dbo].[Sessioni]
ADD CONSTRAINT [FK_UtenteSessione]
    FOREIGN KEY ([Utente_Username])
    REFERENCES [dbo].[Utenti]
        ([Username])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UtenteSessione'
CREATE INDEX [IX_FK_UtenteSessione]
ON [dbo].[Sessioni]
    ([Utente_Username]);
GO

-- Creating foreign key on [Sessione_Id] in table 'Risposte'
ALTER TABLE [dbo].[Risposte]
ADD CONSTRAINT [FK_SessioneRisposta]
    FOREIGN KEY ([Sessione_Id])
    REFERENCES [dbo].[Sessioni]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SessioneRisposta'
CREATE INDEX [IX_FK_SessioneRisposta]
ON [dbo].[Risposte]
    ([Sessione_Id]);
GO

-- Creating foreign key on [StatoSessione_Id] in table 'Sessioni'
ALTER TABLE [dbo].[Sessioni]
ADD CONSTRAINT [FK_StatoSessioneSessione]
    FOREIGN KEY ([StatoSessione_Id])
    REFERENCES [dbo].[StatiSessione]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StatoSessioneSessione'
CREATE INDEX [IX_FK_StatoSessioneSessione]
ON [dbo].[Sessioni]
    ([StatoSessione_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------